//
//  DataManager.swift
//  LiLi
//
//  Created by liuzhu on 2019/1/8.
//  Copyright © 2019 liuzhu. All rights reserved.
//

import UIKit

class DataManager: NSObject {
    class func setupDatabase()->FMDatabaseQueue{
        let path = "LiLiData".docDir()
        let queue = FMDatabaseQueue.init(path: path)
        queue?.inDatabase({ (db) in
            let actionSql = "CREATE TABLE IF NOT EXISTS Action( \n" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, \n" +
                "actionType INTEGER, \n" +
                "createTime TEXT, \n" +
                "updateTime TEXT, \n" +
                "dateStr TEXT, \n" +
                "subDateStr TEXT, \n" +
                "titleName TEXT, \n" +
                "des TEXT, \n" +
                "refreshTag INTEGER, \n" +
                "repeatTag INTEGER, \n" +
                "needTip INTEGER, \n" +
                "status INTEGER, \n" +
                "timeStr INTEGER, \n" +
                "recordName TEXT \n" +  /*相当于actionId*/
            "); \n"
            let remarkSql = "CREATE TABLE IF NOT EXISTS Remark( \n" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, \n" +
                "actionId TEXT, \n" +
                "createTime TEXT, \n" +
                "titleName TEXT, \n" +
                "des TEXT, \n" +
                "refreshTag INTEGER, \n" +
                "imageId TEXT, \n" +
                "recordName TEXT \n" +  /*相当于actionId*/
            "); \n"
            let daySql = "CREATE TABLE IF NOT EXISTS Day( \n" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, \n" +
                "dateStr TEXT, \n" +
                "weekStr TEXT, \n" +
                "chineseDateStr TEXT, \n" +
                "chineseYear TEXT, \n" +
                "dayStatus INTEGER, \n" +
                "status INTEGER, \n" +
                "actions TEXT \n" +
            "); \n"
            if db.executeStatements(actionSql){
                print("启用action表成功")
            }else{
                print("启用action表失败")
            }
            if db.executeStatements(remarkSql){
                print("启用remark表成功")
            }else{
                print("启用remark表失败")
            }
            if db.executeStatements(daySql){
                print("启用day表成功")
            }else{
                print("启用day表失败")
            }
        })
        return queue!
    }
    
    class func isExisThisRecord(tableName:String,record:String,recordName:String) -> Bool{
        let queue = setupDatabase()
        var result = false
        queue.inDatabase { (db) in
            let querySql = "SELECT * FROM \(tableName) WHERE \(record) = '\(recordName)'"
            let res = db.executeQuery(querySql, withArgumentsIn: [])
            var count = 0
            if res == nil{
                result = false
            }else{
                while res!.next(){
                    count += 1
                }
                if count == 0{
                    result = false
                }else{
                    result = true
                }
            }
        }
        queue.close()
        return result
    }
    
    //MARK:- Action表操作
   class func saveAction(action:LLAction,finish:@escaping(_ isSuccess:Bool)->()) {
        let queue = setupDatabase()
        queue.inDatabase { (db) in
            var sql = ""
            if !isExisThisRecord(tableName: "Action",record: "recordName", recordName: action.recordName!){
                sql = "INSERT INTO Action (actionType,createTime,updateTime,dateStr,subDateStr,titleName,des,refreshTag,repeatTag,needTip,status,timeStr,recordName) VALUES (\(Int(action.actionType.rawValue)),'\(action.createTime ?? "0")','\(action.updateTime ?? "0")','\(action.dateStr ?? "0")','\(action.subDateStr ?? "0")','\(action.titleName ?? "0")','\(action.des ?? "0")',\(action.refreshTag ?? 0),\(Int(action.repeatTag.rawValue)),\(action.needTip ?? 0),\(action.status ?? 0),'\(action.timeStr ?? "0")','\(action.recordName ?? "0")');"
            }else{
                sql = "UPDATE Action SET actionType = \(Int(action.actionType.rawValue)),createTime = '\(action.createTime ?? "0")',updateTime = '\(action.updateTime ?? "0")',dateStr = '\(action.dateStr ?? "0")',subDateStr = '\(action.subDateStr ?? "0")',titleName = '\(action.titleName ?? "0")',des = '\(action.des ?? "0")',refreshTag = \(action.refreshTag ?? 0),repeatTag = \(Int(action.repeatTag.rawValue)),needTip = \(action.needTip ?? 0),status = \(action.status ?? 0),timeStr = '\(action.timeStr ?? "0")' WHERE recordName = '\(action.recordName ?? "0")';"
            }
            // 2、执行SQLite语句"
            if (db.executeUpdate(sql, withArgumentsIn: [])){
                print("执行sql成功")
                finish(true)
            }else{
                print("执行sql失败")
                finish(false)
            }
        }
        queue.close()
    }
    
    class func deleteAction(recordName:String,finish:@escaping(_ isSuccess:Bool)->()){
        let queue = setupDatabase()
        queue.inDatabase { (db) in
            var sql = ""
            if !isExisThisRecord(tableName: "Action",record: "recordName", recordName: recordName){
                print("没有该记录\(recordName)")
            }else{
                sql = "DELETE FROM action WHERE recordName = '\(recordName)'"
            }
            // 2、执行SQLite语句"
            if (db.executeUpdate(sql, withArgumentsIn: [])){
                print("执行sql成功")
                //TODO-推送到icloud
                finish(true)
            }else{
                print("执行sql失败")
                finish(false)
            }
        }
        queue.close()
    }
    
    class func QueryAction(recordName:String) -> LLAction? {
        let queue = setupDatabase()
        var model:LLAction?
        queue.inDatabase { (db) in
            if !isExisThisRecord(tableName: "Action",record: "recordName", recordName: recordName){
                model = nil
            }else{
                let sql = "SELECT * FROM Action WHERE recordName = '\(recordName)'"
                let res = db.executeQuery(sql, withArgumentsIn: [])
                model = LLAction()
                while res!.next() {
                    model!.recordName = recordName
                    model!.actionType = ActionType(rawValue: Int((res?.int(forColumn: "actionType"))!))!
                    model!.createTime = res?.string(forColumn: "createTime")
                    model!.updateTime = res?.string(forColumn: "updateTime")
                    model!.dateStr = res?.string(forColumn: "dateStr")
                    model!.subDateStr = res?.string(forColumn: "subDateStr")
                    model!.titleName = res?.string(forColumn: "titleName")
                    model!.des = res?.string(forColumn: "des")
                    model!.refreshTag = res?.int(forColumn: "refreshTag")
                    model!.repeatTag = ActionRepeatTag(rawValue: Int((res?.int(forColumn: "repeatTag"))!))!
                    model!.needTip = res?.int(forColumn: "needTip")
                    model!.timeStr = res?.string(forColumn: "timeStr")
                    model!.status = res?.int(forColumn: "status")
                }
            }
        }
        queue.close()
        return model
    }
    
    class func QueryActions(finish:@escaping(_ actions:[LLAction]?, _ isSuccess:Bool)->()){
        let queue = setupDatabase()
        queue.inDatabase { (db) in
            let sql = "SELECT * FROM Action WHERE 1 = 1"
            let res = db.executeQuery(sql, withArgumentsIn: [])
            var models = [LLAction]()
            while res!.next() {
                let model = LLAction()
                model.recordName = res?.string(forColumn: "recordName")
                model.actionType = ActionType(rawValue: Int((res?.int(forColumn: "actionType"))!))!
                model.createTime = res?.string(forColumn: "createTime")
                model.updateTime = res?.string(forColumn: "updateTime")
                model.dateStr = res?.string(forColumn: "dateStr")
                model.subDateStr = res?.string(forColumn: "subDateStr")
                model.titleName = res?.string(forColumn: "titleName")
                model.des = res?.string(forColumn: "des")
                model.refreshTag = res?.int(forColumn: "refreshTag")
                model.repeatTag = ActionRepeatTag(rawValue: Int((res?.int(forColumn: "repeatTag"))!))!
                model.needTip = res?.int(forColumn: "needTip")
                model.status = res?.int(forColumn: "status")
                model.timeStr = res?.string(forColumn: "timeStr")
                models.append(model)
            }
            if models.count > 0{
                finish(models,true)
            }else{
                finish(nil,false)
            }
        }
        queue.close()
    }
    
    class func QueryActionsWithDateStr(dateStr:String,finish:@escaping(_ actions:[LLAction]?, _ isSuccess:Bool)->()){
        let queue = setupDatabase()
        queue.inDatabase { (db) in
            let sql = "SELECT * FROM Action WHERE dateStr = '\(dateStr)'"
            let res = db.executeQuery(sql, withArgumentsIn: [])
            var models = [LLAction]()
            while res!.next() {
                let model = LLAction()
                model.recordName = res?.string(forColumn: "recordName")
                model.actionType = ActionType(rawValue: Int((res?.int(forColumn: "actionType"))!))!
                model.createTime = res?.string(forColumn: "createTime")
                model.updateTime = res?.string(forColumn: "updateTime")
                model.dateStr = res?.string(forColumn: "dateStr")
                model.subDateStr = res?.string(forColumn: "subDateStr")
                model.titleName = res?.string(forColumn: "titleName")
                model.des = res?.string(forColumn: "des")
                model.refreshTag = res?.int(forColumn: "refreshTag")
                model.repeatTag = ActionRepeatTag(rawValue: Int((res?.int(forColumn: "repeatTag"))!))!
                model.needTip = res?.int(forColumn: "needTip")
                model.status = res?.int(forColumn: "status")
                model.timeStr = res?.string(forColumn: "timeStr")
                models.append(model)
            }
            if models.count > 0{
                finish(models,true)
            }else{
                finish(nil,false)
            }
        }
        queue.close()
    }
    
    class func LikeActions(dateStr:String)->[LLAction]?{
        let queue = setupDatabase()
        var models:[LLAction]?
        queue.inDatabase { (db) in
            let sql = "SELECT * FROM Action WHERE status = 0 AND subDateStr like '%%\(dateStr)%%' or dateStr = '\(dateStr)'"
            let res = db.executeQuery(sql, withArgumentsIn: [])
            if res == nil{
                models = nil
            }else{
                models = [LLAction]()
                while res!.next() {
                    let model = LLAction()
                    model.recordName = res?.string(forColumn: "recordName")
                    model.actionType = ActionType(rawValue: Int((res?.int(forColumn: "actionType"))!))!
                    model.createTime = res?.string(forColumn: "createTime")
                    model.updateTime = res?.string(forColumn: "updateTime")
                    model.dateStr = res?.string(forColumn: "dateStr")
                    model.subDateStr = res?.string(forColumn: "subDateStr")
                    model.titleName = res?.string(forColumn: "titleName")
                    model.des = res?.string(forColumn: "des")
                    model.refreshTag = res?.int(forColumn: "refreshTag")
                    model.repeatTag = ActionRepeatTag(rawValue: Int((res?.int(forColumn: "repeatTag"))!))!
                    model.needTip = res?.int(forColumn: "needTip")
                    model.status = res?.int(forColumn: "status")
                    model.timeStr = res?.string(forColumn: "timeStr")
                    models!.append(model)
                }
            }
        }
        queue.close()
        return models
    }
    
    class func getActionCount() -> Int{
        let queue = setupDatabase()
        var count = 0
        queue.inDatabase { (db) in
            let sql = "SELECT COUNT(*) FROM Action WHERE status = 0"
            let res = db.executeQuery(sql, withArgumentsIn: [])
            if res == nil{
               count = 0
            }else{
                while res!.next(){
                    count += 1
                }
            }
        }
        queue.close()
        return count
    }
    
    //MARK:- Remark 表操作
    class func saveRemark(remark:LLRemark,finish:@escaping(_ isSuccess:Bool)->()) {
        let queue = setupDatabase()
        queue.inDatabase { (db) in
            var sql = ""
            if !isExisThisRecord(tableName: "Remark", record: "recordName",recordName: remark.recordName!){
                sql = "INSERT INTO Remark (actionId,createTime,titleName,des,refreshTag,imageId,recordName) VALUES ('\(remark.actionId ?? "0")','\(remark.createTime ?? "0")','\(remark.titleName ?? "0")','\(remark.des ?? "0")',\(remark.refreshTag ?? 0),'\(remark.imageId ?? "0")','\(remark.recordName ?? "0")');"
            }else{
                sql = "UPDATE Remark SET actionId = '\(remark.actionId ?? "0")',createTime = '\(remark.createTime ?? "0")',titleName = '\(remark.titleName ?? "0")',des = '\(remark.des ?? "0")',refreshTag = \(remark.refreshTag ?? 0) WHERE recordName = '\(remark.recordName ?? "0")';"
            }
            // 2、执行SQLite语句"
            if (db.executeUpdate(sql, withArgumentsIn: [])){
                print("执行sql成功")
                finish(true)
            }else{
                finish(false)
                print("执行sql失败")
            }
        }
       queue.close()
    }
    
    class func deleteRemark(recordName:String,finish:@escaping(_ isSuccess:Bool)->()){
        let queue = setupDatabase()
        queue.inDatabase { (db) in
            var sql = ""
            if !isExisThisRecord(tableName: "Remark",record: "recordName", recordName: recordName){
                print("没有该记录\(recordName)")
            }else{
                sql = "DELETE FROM Remark WHERE recordName = '\(recordName)'"
            }
            // 2、执行SQLite语句"
            if (db.executeUpdate(sql, withArgumentsIn: [])){
                print("执行sql成功")
                finish(true)
            }else{
                finish(false)
                print("执行sql失败")
            }
        }
        queue.close()
    }
    
    class func QueryRemark(recordName:String) -> LLRemark? {
        let queue = setupDatabase()
        var model:LLRemark?
        queue.inDatabase { (db) in
            if !isExisThisRecord(tableName: "Remark", record: "recordName",recordName: recordName){
                model = nil
            }else{
                let sql = "SELECT * FROM Remark WHERE recordName = '\(recordName)'"
                let res = db.executeQuery(sql, withArgumentsIn: [])
                model = LLRemark()
                while res!.next() {
                    model!.recordName = recordName
                    model!.actionId = res?.string(forColumn: "actionId")
                    model!.createTime = res?.string(forColumn: "createTime")
                    model!.titleName = res?.string(forColumn: "titleName")
                    model!.des = res?.string(forColumn: "des")
                    model!.refreshTag = res?.int(forColumn: "refreshTag")
                    model!.imageId = res?.string(forColumn: "imageId")
                }
            }
        }
        queue.close()
        return model
    }
    
    class func QueryRemarks(actionId:String) -> [LLRemark]? {
        let queue = setupDatabase()
        var models:[LLRemark]?
        queue.inDatabase { (db) in
            let sql = "SELECT * FROM Remark WHERE actionId = '\(actionId)'"
            let res = db.executeQuery(sql, withArgumentsIn: [])
            models = [LLRemark]()
            while res!.next() {
                let model = LLRemark()
                model.recordName = res?.string(forColumn: "recordName")
                model.actionId = res?.string(forColumn: "actionId")
                model.createTime = res?.string(forColumn: "createTime")
                model.titleName = res?.string(forColumn: "titleName")
                model.des = res?.string(forColumn: "des")
                model.refreshTag = res?.int(forColumn: "refreshTag")
                model.imageId = res?.string(forColumn: "imageId")
                models!.append(model)
            }
        }
        queue.close()
        return models
    }
    
    //MARK:- Day的数据表（这个表只是本地做缓存用，可以理解为归档，iCloud中是没有这个表的）
    class func saveDays(days:[LLDay],finish:@escaping(_ isSuccess:Bool)->()) {
        let queue = setupDatabase()
        queue.inDatabase { (db) in
            var sql = ""
            for day in days{
                var actionIds = ""
                var i = 0
                for action in day.actions{
                    i = i + 1
                    if i == 1{
                        actionIds = actionIds + action.recordName!
                    }else{
                        actionIds = actionIds + ";" + action.recordName!
                    }
                }
                if isExisThisRecord(tableName: "Day", record: "dateStr", recordName: day.dateStr ?? "0"){
                    sql = "UPDATE Day SET weekStr = '\(day.weekStr ?? "0")',chineseDateStr = '\(day.chineseDateStr ?? "0")',chineseYear = '\(day.chineseYear ?? "0")',dayStatus = \(Int(day.dayStatus.rawValue)),status = \(day.status ?? 0),actions = '\(actionIds)' WHERE dateStr = '\(day.dateStr ?? "0")';"
                }else{
                    sql = "INSERT INTO Day (dateStr,weekStr,chineseDateStr,chineseYear,dayStatus,status,actions) VALUES ('\(day.dateStr ?? "0")','\(day.weekStr ?? "0")','\(day.chineseDateStr ?? "0")','\(day.chineseYear ?? "0")',\(Int(day.dayStatus.rawValue)),\(day.status ?? 0),'\(actionIds)');"
                }
                if (db.executeUpdate(sql, withArgumentsIn: [])){
                    print("执行sql成功")
                    finish(true)
                }else{
                    finish(false)
                    print("执行sql失败")
                }
            }
        }
    }
    
   class func QueryDays() -> [LLDay]? {
        let queue = setupDatabase()
        var models:[LLDay]?
        queue.inDatabase { (db) in
            let sql = "SELECT * FROM Day WHERE 1 = 1"
            let res = db.executeQuery(sql, withArgumentsIn: [])
            models = [LLDay]()
            while res!.next() {
                let model = LLDay()
                model.dateStr = res?.string(forColumn: "dateStr")
                model.weekStr = res?.string(forColumn: "weekStr")
                model.chineseDateStr = res?.string(forColumn: "chineseDateStr")
                model.chineseYear = res?.string(forColumn: "chineseYear")
                model.dayStatus = DayStatusEnum(rawValue: Int((res?.int(forColumn: "dayStatus"))!))!
                model.status = res?.int(forColumn: "status")
                let actionIds = res?.string(forColumn: "actions")
                var actions = [LLAction]()
                if actionIds != nil && actionIds!.count != 0{
                    var arr = [String]()
                    if !actionIds!.contains(";")
                    {
                        arr.append(actionIds!)
                    }else{
                        arr = actionIds!.components(separatedBy: ";")
                    }
                    for str in arr{
                        let action = QueryAction(recordName: str)
                        if action != nil{
                            actions.append(action!)
                        }
                    }
                }
                model.actions = actions
                models!.append(model)
            }
        }
        return models
    }
    
    class func QueryDay(dateStr:String) -> LLDay? {
        let queue = setupDatabase()
        var model:LLDay?
        queue.inDatabase { (db) in
            if !isExisThisRecord(tableName: "Day",record: "dateStr" ,recordName: dateStr){
                model = nil
            }else{
                let sql = "SELECT * FROM Day WHERE dateStr = '\(dateStr)'"
                let res = db.executeQuery(sql, withArgumentsIn: [])
                model = LLDay()
                while res!.next() {
                    model!.dateStr = res?.string(forColumn: "dateStr")
                    model!.weekStr = res?.string(forColumn: "weekStr")
                    model!.chineseDateStr = res?.string(forColumn: "chineseDateStr")
                    model!.chineseYear = res?.string(forColumn: "chineseYear")
                    model!.dayStatus = DayStatusEnum(rawValue: Int((res?.int(forColumn: "dayStatus"))!))!
                    model!.status = res?.int(forColumn: "status")
                    let actionIds = res?.string(forColumn: "actions")
                    var actions = [LLAction]()
                    if actionIds != nil && actionIds!.count != 0{
                        var arr = [String]()
                        if !actionIds!.contains(";")
                        {
                            arr.append(actionIds!)
                        }else{
                            arr = actionIds!.components(separatedBy: ";")
                        }
                        for str in arr{
                            let action = QueryAction(recordName: str)
                            if action != nil{
                                actions.append(action!)
                            }
                        }
                    }
                    model!.actions = actions
                }
            }
        }
        queue.close()
        return model
    }
}
